﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using DatabaseHelper;

namespace UserControlLibrary
{
    /// <summary>
    /// This user control's used to create complex project tree and event list
    /// </summary>
    public partial class TreeListView : UserControl
    {
        #region PROPERTIES
        /// <summary>
        /// Number of levels when build tree view
        /// \nLevel 0: Project 
        /// \nLevel 1: Project | Pipeline 
        /// \nLevel 2: Project | Pipeline | Record
        /// \nLevel 2: Project | Pipeline | Record | Event
        /// </summary>
        public int TreeLevel { get; set; }
        /// <summary>
        /// With or without checkbox in treenode
        /// \nTrue: with checkbox
        /// \nFalse: without checkbox
        /// </summary>
        public bool CellClickEnabled { get; set; }
        public bool WithCheckbox { get; set; }
        /// <summary>
        /// DBHelper for database connection
        /// </summary>
        private DBHelper dh;
        /// <summary>
        /// BindingSource of event list
        /// </summary>
        private BindingSource bds;
        /// <summary>
        /// DataTable of event list
        /// </summary>
        private DataTable dt;
        /// <summary>
        /// List of treenodes with checked box
        /// </summary>
        private List<string> lstRecord; 
        #endregion

        #region CONSTRUCTORS
        /// <summary>
        /// Default constructor
        /// </summary>
        public TreeListView()
            :this(2,false, false)
        {
        }
        /// <summary>
        /// Constructor with all properties
        /// </summary>
        /// <param name="level">Tree level</param>
        /// <param name="bWithCB">With (true) or without (false) check box</param>
        public TreeListView(int level, bool bWithCB, bool bCellClickEnabled)
        {
            dh = new DBHelper();
            lstRecord = new List<string>();
            bds = new BindingSource();
            dt = new DataTable();
            // Auto code
            InitializeComponent();
            // Init public properties
            TreeLevel = level;
            WithCheckbox = bWithCB;
            CellClickEnabled = bCellClickEnabled;
            /*
            // Init private properties
            dh = new DBHelper();
            bds = new BindingSource();
            dt = new DataTable();

            // Build the datagridview
            dh.Query = "SELECT * FROM Events WHERE RecordID = 0";
            dt = dh.Fill();
            bds.DataSource = dt;
            dgv.DataSource = bds;

            // Build the treeview
            BuildTV();
            tv.CheckBoxes = WithCheckbox;
            tv.ExpandAll();
            */
        }
        #endregion

        #region PUBLIC METHODS
        /// <summary>
        /// Refresh the TreeListView user interface with record id
        /// </summary>
        public void RefreshTLV(string recID)
        {
            // Build the datagridview
            BuildDGV(recID);

            // Build the treeview
            BuildTV();
            tv.CheckBoxes = WithCheckbox;
            tv.ExpandAll();
        }
        /// <summary>
        /// Refresh the TreeListView user interface 
        /// </summary>
        public void RefreshTLV()
        {
            // Build the datagridview
            dh.Query = "SELECT * FROM Events WHERE RecordID = 0";
            dt = dh.Fill();
            bds.DataSource = dt;
            dgv.DataSource = bds;

            // Build the treeview
            BuildTV();
            tv.CheckBoxes = WithCheckbox;
            tv.ExpandAll();
        }
        /// <summary>
        /// Update data from datagridview to database
        /// </summary>
        public void UpdateDB()
        {
            dh.Query = "SELECT * FROM Events";
            dh.Update(dt);
        }

        public void AddEvent(PEvent evnt, string recID)
        {
            dh.Add(evnt, recID);
            BuildDGV(recID);
        }
        public void DeleteEvents()
        {
            foreach (DataGridViewRow dr in dgv.SelectedRows)
            {
                dgv.Rows.Remove(dr);
            }
            UpdateDB();
        }
        public int AddRecord(PRecord rec, string pipID)
        {
            int lastInsertedRecordId;
            lastInsertedRecordId = dh.Add(rec, pipID);
            RefreshTLV(lastInsertedRecordId.ToString());
            return lastInsertedRecordId;
        }

        public int UpdateRecord(int recId, PRecord rec, string pipID)
        {
            int lastInsertedRecordId;
            lastInsertedRecordId = dh.Update(recId, rec, pipID);
            RefreshTLV(lastInsertedRecordId.ToString());
            return lastInsertedRecordId;
        }
        #endregion

        public void ExportToExcel()
        {
            // creating Excel Application
            Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
            
            // creating new WorkBook within Excel application
            Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
            
            // creating new Excelsheet in workbook
            Microsoft.Office.Interop.Excel._Worksheet worksheet = null;

            // see the excel sheet behind the program
            app.Visible = true;

            try
            {
                // get the reference of first sheet. By default its name is Sheet1.
                // store its reference to worksheet
                worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets["Sheet1"];
                worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.ActiveSheet;

                // changing the name of active sheet
                worksheet.Name = "Exported from CNA Softwares";

                // storing header part in Excel
                for (int i = 1; i < dgv.Columns.Count + 1; i++)
                {
                    worksheet.Cells[1, i] = dgv.Columns[i - 1].HeaderText;
                }

                // storing Each row and column value to excel sheet
                for (int i = 0; i < dgv.Rows.Count - 1; i++)
                {
                    for (int j = 0; j < dgv.Columns.Count; j++)
                    {
                        worksheet.Cells[i + 2, j + 1] = dgv.Rows[i].Cells[j].Value.ToString();
                    }
                }
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }
        #region PRIVATE METHODS
        /// <summary>
        /// Build Datagridview with all events
        /// </summary>
        private void BuildDGV()
        {
            // Display all events on datagridview
            dh.Query = "SELECT * FROM Events";
            dt = dh.Fill();
            bds.DataSource = dt;
            dgv.DataSource = bds;
        }
        private void BuildDGV(string recID)
        {
            // Display all events on datagridview
            dh.Query = "SELECT * FROM Events WHERE RecordID = " + recID;
            dt = dh.Fill();
            bds.DataSource = dt;
            dgv.DataSource = bds;
        }
        /// <summary>
        /// Build Treeview with different levels
        /// </summary>
        private void BuildTV()
        {
            tv.Nodes.Clear();
            if ((TreeLevel > -1) && (TreeLevel < 4))
            {
                string lv0Query = "SELECT ProjectID, ProjectName FROM Projects";
                dh.Query = lv0Query;
                DataTable lv0Table = dh.Fill();

                for (int i = 0; i < lv0Table.Rows.Count; i++)
                {
                    string lv0str = lv0Table.Rows[i]["ProjectName"].ToString();
                    tv.Nodes.Add(lv0str);

                    if (TreeLevel > 0)
                    {
                        string lv1Query = "SELECT PipelineID, PipelineName FROM Pipelines WHERE ProjectID=" + lv0Table.Rows[i].ItemArray[0].ToString();
                        dh.Query = lv1Query;
                        DataTable lv1Table = dh.Fill();
                        for (int j = 0; j < lv1Table.Rows.Count; j++)
                        {
                            string lv1str = lv1Table.Rows[j].ItemArray[1].ToString();
                            tv.Nodes[i].Nodes.Add(lv1str);
                            if (TreeLevel > 1)
                            {
                                string lv2Query = "SELECT RecordID, KPStart, KPEnd, TimeStart FROM Records WHERE PipelineID=" + lv1Table.Rows[j].ItemArray[0].ToString();
                                //string lv2Query = "SELECT RecordID FROM Records WHERE PipelineID=" + lv1Table.Rows[j].ItemArray[0].ToString();
                                dh.Query = lv2Query;
                                DataTable lv2Table = dh.Fill();
                                for (int k = 0; k < lv2Table.Rows.Count; k++)
                                {
                                    string lv2str =
                                        "KP " + lv2Table.Rows[k]["KPStart"].ToString() +
                                        " - KP " + lv2Table.Rows[k]["KPEnd"].ToString() +
                                        " @ " + lv2Table.Rows[k]["TimeStart"].ToString() +
                                        " REC#" + lv2Table.Rows[k]["RecordID"].ToString();
                                    tv.Nodes[i].Nodes[j].Nodes.Add(lv2str);
                                    if (TreeLevel > 2)
                                    {
                                        string lv3Query = "SELECT Description FROM Events WHERE RecordID=" + lv2Table.Rows[k].ItemArray[0].ToString();
                                        dh.Query = lv3Query;
                                        DataTable lv3Table = dh.Fill();
                                        for (int h = 0; h < lv3Table.Rows.Count; h++)
                                        {
                                            string lv3str = lv3Table.Rows[k].ItemArray[0].ToString();
                                            tv.Nodes[i].Nodes[j].Nodes[k].Nodes.Add(lv3str);
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
            else
            {
                MessageBox.Show("Tree level must be between 0 and 3.");
            }
        }
        /// <summary>
        /// Updates check box of all child tree nodes recursively under parent node
        /// </summary>
        /// <param name="treeNode">Parent node</param>
        /// <param name="nodeChecked">Node checked if true and vice versa</param>
        private void CheckAllChildNodes(TreeNode treeNode, bool nodeChecked)
        {
            foreach (TreeNode node in treeNode.Nodes)
            {
                // Check/clear this node
                node.Checked = nodeChecked;
                if (node.Nodes.Count > 0)
                {
                    // If the current node has child nodes, 
                    // call the CheckAllChildsNodes method recursively. 
                    this.CheckAllChildNodes(node, nodeChecked);
                }
            }
        }
        /// <summary>
        /// Find all checked records recursively under parent node
        /// \nRecords are located at level 2 of the treeview
        /// </summary>
        /// <param name="treeNode">Parent node</param>
        private void FindAllCheckedRecords(TreeNode treeNode)
        {
            foreach (TreeNode node in treeNode.Nodes)
            {
                if (node.Nodes.Count > 0)
                {
                    // If the current node has child nodes, 
                    // call the FindAllCheckedRecords method recursively. 
                    this.FindAllCheckedRecords(node);
                }
                // If node text start with "KP" string, it's the record
                if ((node.Checked == true) && (node.Text.StartsWith("KP")))
                {
                    // Add found record to the record list
                    lstRecord.Add(node.Text);
                }
            }
        }
        /// <summary>
        /// Display all events associated with all checked records 
        /// </summary>
        private void DisplaySelectedEvents()
        {
            foreach (TreeNode node in tv.Nodes)
            {
                FindAllCheckedRecords(node);
            }

            string str = "";
            foreach (string item in lstRecord)
            {
                string id = item.Substring(item.IndexOf("REC#") + 4);
                str += id + ",";
            }
            if (str != "")
            {
                dh.Query = "SELECT * FROM Events WHERE RecordID IN (" + str + ")";
                dt = dh.Fill();
                bds.DataSource = dt;
                dgv.DataSource = bds;
                lstRecord.Clear();
            }
            else
            {
                dh.Query = "SELECT * FROM Events WHERE RecordID = 0";
                dt = dh.Fill();
                bds.DataSource = dt;
                dgv.DataSource = bds;
            }
        } 
        #endregion

        #region EVENTS
        /// <summary>
        /// This event is raised when a cell is clicked
        /// \nThis event used to locate the video position of the event
        /// </summary>
        public event EventHandler<EventListCellClickEventArgs> EventListCellClick;
        /// <summary>
        /// Raise the cell click event with data (PRecord of the event)
        /// </summary>
        /// <param name="e">Event data</param>
        protected virtual void OnEventListCellClick(EventListCellClickEventArgs e)
        {
            EventHandler<EventListCellClickEventArgs> handler = EventListCellClick;
            if (handler != null)
            {
                handler(this, e);
            }
        }
        /// <summary>
        /// Handle after the tree node checkbox is checked
        /// </summary>
        /// <remarks>
        /// This code can be added to the BeforeCheck event handler instead of the AfterCheck event.
        /// \nAfter a tree node's Checked property is changed, all its child nodes are updated to the same value. 
        /// </remarks>
        /// <param name="sender">Event Sender</param>
        /// <param name="e">Event Arguments</param>
        private void node_AfterCheck(object sender, TreeViewEventArgs e)
        {
            DisplaySelectedEvents();
            // The code only executes if the user caused the checked state to change. 
            if (e.Action != TreeViewAction.Unknown)
            {
                if (e.Node.Nodes.Count > 0)
                {
                    /* Calls the CheckAllChildNodes method, passing in the current 
                    Checked value of the TreeNode whose checked state changed. */
                    this.CheckAllChildNodes(e.Node, e.Node.Checked);
                }
            }
        }
        /// <summary>
        /// Handle when a row or rows are deleted from the DataGridView.
        /// </summary>
        /// <param name="sender">Event Sender</param>
        /// <param name="e">Event Arguments</param>
        private void dgv_RowsRemoved(object sender, DataGridViewRowsRemovedEventArgs e)
        {
            // Update the database
            UpdateDB();
        }
        /// <summary>
        /// Handle when a cell in the DataGridView is clicked
        /// \nWhen a cell is clicked, a event is raised and its PRecord is passed
        /// \nThis event is used to go to video position 
        /// </summary>
        /// <param name="sender">Event Sender</param>
        /// <param name="e">Event Sender</param>
        private void dgv_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            // Create a EventListCellClick argument
            EventListCellClickEventArgs args = new EventListCellClickEventArgs();
            
            // Enable cell click only when dgv is not empty 
            if (dgv.Rows.Count > 1)
            {
                // Find PRecord of this PEvent in the database by the RecordID 
                DataGridViewRow row = dgv.CurrentRow;
                int id = int.Parse(row.Cells["RecordID"].Value.ToString());
                args.SelectedRecord = dh.FindRecord(id);
                args.SelectedEvent = new PEvent();
                args.SelectedEvent.EventTime = DateTime.Parse(row.Cells["EventTime"].Value.ToString());
                args.SelectedEvent.Description = row.Cells["Description"].Value.ToString();
                args.SelectedEvent.GeneralComment = row.Cells["GeneralComment"].Value.ToString();
                args.SelectedEvent.Comment = row.Cells["Comment"].Value.ToString();
                args.SelectedEvent.KP = row.Cells["KP"].Value.ToString();
                args.SelectedEvent.Easting = row.Cells["Easting"].Value.ToString();
                args.SelectedEvent.Northing = row.Cells["Northing"].Value.ToString();

                // Raise event and pass argument
                OnEventListCellClick(args);
            }
        } 
        #endregion

        private void TreeListView_Load(object sender, EventArgs e)
        {
            if (this.DesignMode) return;

            // Init private properties
            dh = new DBHelper();
            bds = new BindingSource();
            dt = new DataTable();

            // Enable cell click event
            if (CellClickEnabled)
            {
                dgv.CellClick += new DataGridViewCellEventHandler(dgv_CellClick);
            }

            // Build the datagridview
            dh.Query = "SELECT * FROM Events WHERE RecordID = 0";
            dt = dh.Fill();
            bds.DataSource = dt;
            dgv.DataSource = bds;

            // Build the treeview
            BuildTV();
            tv.CheckBoxes = WithCheckbox;
            tv.ExpandAll();
        }
    }
}
